from typing import List, Optional, Dict, Any
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select
from sqlalchemy import update, delete, and_, or_
from datetime import datetime
from server.db.models.server import Server, ServerConfig, ServerStatusHistory, ServerMod, ServerBackup, ServerLog
from server.db.models.user import User
from server.schemas.server import ServerCreate, ServerUpdate, ServerConfigCreate, ServerConfigUpdate, ServerStatusHistoryCreate

class ServerRepository:
    def __init__(self, db_session: AsyncSession):
        self.db_session = db_session

    async def create_server(self, server: ServerCreate, user_id: int) -> Server:
        """创建新服务器"""
        db_server = Server(
            name=server.name,
            description=server.description,
            type=server.type,
            ip=server.ip,
            port=server.port,
            rcon_port=server.rcon_port,
            rcon_password=server.rcon_password,
            max_players=server.max_players,
            install_path=server.install_path,
            executable_path=server.executable_path,
            steam_app_id=server.steam_app_id,
            steam_branch=server.steam_branch,
            auto_start=server.auto_start,
            created_by=user_id,
            cpu_limit=server.cpu_limit,
            memory_limit=server.memory_limit,
            bandwidth_limit=server.bandwidth_limit,
            is_public=server.is_public,
            public_description=server.public_description,
            public_image=server.public_image
        )
        self.db_session.add(db_server)
        await self.db_session.commit()
        await self.db_session.refresh(db_server)
        return db_server

    async def get_server_by_id(self, server_id: int) -> Optional[Server]:
        """根据ID获取服务器"""
        result = await self.db_session.execute(select(Server).filter(Server.id == server_id))
        return result.scalars().first()

    async def get_servers_by_user(self, user_id: int, skip: int = 0, limit: int = 100) -> List[Server]:
        """获取用户创建的服务器列表"""
        result = await self.db_session.execute(
            select(Server)
            .filter(Server.created_by == user_id)
            .offset(skip)
            .limit(limit)
        )
        return result.scalars().all()

    async def get_public_servers(self, skip: int = 0, limit: int = 100) -> List[Server]:
        """获取公开的服务器列表"""
        result = await self.db_session.execute(
            select(Server)
            .filter(and_(Server.is_public == True, Server.status == 'running'))
            .offset(skip)
            .limit(limit)
        )
        return result.scalars().all()

    async def get_all_servers(self, skip: int = 0, limit: int = 100) -> List[Server]:
        """获取所有服务器列表(管理员用)"""
        result = await self.db_session.execute(
            select(Server)
            .offset(skip)
            .limit(limit)
        )
        return result.scalars().all()

    async def update_server(self, server_id: int, server_update: ServerUpdate) -> Optional[Server]:
        """更新服务器信息"""
        server = await self.get_server_by_id(server_id)
        if not server:
            return None

        update_data = server_update.dict(exclude_unset=True)
        for key, value in update_data.items():
            setattr(server, key, value)

        await self.db_session.commit()
        await self.db_session.refresh(server)
        return server

    async def delete_server(self, server_id: int) -> bool:
        """删除服务器"""
        server = await self.get_server_by_id(server_id)
        if not server:
            return False

        await self.db_session.delete(server)
        await self.db_session.commit()
        return True

    async def update_server_status(self, server_id: int, new_status: str, user_id: Optional[int] = None, comment: Optional[str] = None) -> Optional[ServerStatusHistory]:
        """更新服务器状态并记录历史"""
        server = await self.get_server_by_id(server_id)
        if not server:
            return None

        old_status = server.status
        server.status = new_status

        # 记录状态变更时间
        if new_status == 'running':
            server.last_start_time = datetime.utcnow()
        elif new_status in ['stopped', 'error']:
            server.last_stop_time = datetime.utcnow()

        # 创建状态历史记录
        status_history = ServerStatusHistory(
            server_id=server_id,
            old_status=old_status,
            new_status=new_status,
            changed_by=user_id,
            comment=comment
        )
        self.db_session.add(status_history)

        await self.db_session.commit()
        await self.db_session.refresh(server)
        await self.db_session.refresh(status_history)
        return status_history

    # ------------------------------
    # 服务器配置相关方法
    # ------------------------------
    async def create_server_config(self, config: ServerConfigCreate) -> ServerConfig:
        """创建服务器配置"""
        db_config = ServerConfig(**config.dict())
        self.db_session.add(db_config)
        await self.db_session.commit()
        await self.db_session.refresh(db_config)
        return db_config

    async def get_server_configs(self, server_id: int) -> List[ServerConfig]:
        """获取服务器所有配置"""
        result = await self.db_session.execute(
            select(ServerConfig)
            .filter(ServerConfig.server_id == server_id)
        )
        return result.scalars().all()

    async def get_server_config_by_key(self, server_id: int, config_key: str) -> Optional[ServerConfig]:
        """根据键获取服务器配置"""
        result = await self.db_session.execute(
            select(ServerConfig)
            .filter(and_(
                ServerConfig.server_id == server_id,
                ServerConfig.config_key == config_key
            ))
        )
        return result.scalars().first()

    async def update_server_config(self, config_id: int, config_update: ServerConfigUpdate) -> Optional[ServerConfig]:
        """更新服务器配置"""
        result = await self.db_session.execute(
            select(ServerConfig)
            .filter(ServerConfig.id == config_id)
        )
        config = result.scalars().first()
        if not config:
            return None

        update_data = config_update.dict(exclude_unset=True)
        for key, value in update_data.items():
            setattr(config, key, value)

        await self.db_session.commit()
        await self.db_session.refresh(config)
        return config

    async def delete_server_config(self, config_id: int) -> bool:
        """删除服务器配置"""
        result = await self.db_session.execute(
            select(ServerConfig)
            .filter(ServerConfig.id == config_id)
        )
        config = result.scalars().first()
        if not config:
            return False

        await self.db_session.delete(config)
        await self.db_session.commit()
        return True

    async def batch_update_configs(self, server_id: int, configs: Dict[str, str]) -> List[ServerConfig]:
        """批量更新服务器配置"""
        updated_configs = []
        for key, value in configs.items():
            config = await self.get_server_config_by_key(server_id, key)
            if config:
                config.config_value = value
                await self.db_session.commit()
                await self.db_session.refresh(config)
                updated_configs.append(config)
            else:
                new_config = ServerConfig(
                    server_id=server_id,
                    config_key=key,
                    config_value=value,
                    is_custom=True
                )
                self.db_session.add(new_config)
                await self.db_session.commit()
                await self.db_session.refresh(new_config)
                updated_configs.append(new_config)
        return updated_configs

    # ------------------------------
    # 服务器状态历史相关方法
    # ------------------------------
    async def get_server_status_history(self, server_id: int, skip: int = 0, limit: int = 100) -> List[ServerStatusHistory]:
        """获取服务器状态历史记录"""
        result = await self.db_session.execute(
            select(ServerStatusHistory)
            .filter(ServerStatusHistory.server_id == server_id)
            .order_by(ServerStatusHistory.created_at.desc())
            .offset(skip)
            .limit(limit)
        )
        return result.scalars().all()

    # ------------------------------
    # 服务器模组相关方法
    # ------------------------------
    async def add_server_mod(self, server_id: int, mod_data: Dict[str, Any]) -> ServerMod:
        """添加服务器模组"""
        mod = ServerMod(
            server_id=server_id,
            mod_id=mod_data.get('mod_id'),
            name=mod_data.get('name'),
            version=mod_data.get('version'),
            file_path=mod_data.get('file_path'),
            workshop_id=mod_data.get('workshop_id'),
            enabled=mod_data.get('enabled', True),
            load_order=mod_data.get('load_order', 0),
            hash=mod_data.get('hash'),
            size=mod_data.get('size')
        )
        self.db_session.add(mod)
        await self.db_session.commit()
        await self.db_session.refresh(mod)
        return mod

    async def get_server_mods(self, server_id: int) -> List[ServerMod]:
        """获取服务器所有模组"""
        result = await self.db_session.execute(
            select(ServerMod)
            .filter(ServerMod.server_id == server_id)
            .order_by(ServerMod.load_order)
        )
        return result.scalars().all()

    async def update_server_mod(self, mod_id: int, mod_data: Dict[str, Any]) -> Optional[ServerMod]:
        """更新服务器模组"""
        result = await self.db_session.execute(
            select(ServerMod)
            .filter(ServerMod.id == mod_id)
        )
        mod = result.scalars().first()
        if not mod:
            return None

        for key, value in mod_data.items():
            if hasattr(mod, key):
                setattr(mod, key, value)

        await self.db_session.commit()
        await self.db_session.refresh(mod)
        return mod

    async def delete_server_mod(self, mod_id: int) -> bool:
        """删除服务器模组"""
        result = await self.db_session.execute(
            select(ServerMod)
            .filter(ServerMod.id == mod_id)
        )
        mod = result.scalars().first()
        if not mod:
            return False

        await self.db_session.delete(mod)
        await self.db_session.commit()
        return True

    # ------------------------------
    # 服务器备份相关方法
    # ------------------------------
    async def create_backup_record(self, backup_data: Dict[str, Any]) -> ServerBackup:
        """创建备份记录"""
        backup = ServerBackup(
            server_id=backup_data.get('server_id'),
            backup_name=backup_data.get('backup_name'),
            backup_path=backup_data.get('backup_path'),
            size=backup_data.get('size'),
            compression=backup_data.get('compression', 'zstd'),
            version=backup_data.get('version'),
            status=backup_data.get('status', 'completed'),
            duration=backup_data.get('duration'),
            created_by=backup_data.get('created_by'),
            is_auto=backup_data.get('is_auto', False),
            retention_days=backup_data.get('retention_days', 7),
            checksum=backup_data.get('checksum')
        )
        self.db_session.add(backup)
        await self.db_session.commit()
        await self.db_session.refresh(backup)
        return backup

    async def get_server_backups(self, server_id: int) -> List[ServerBackup]:
        """获取服务器备份列表"""
        result = await self.db_session.execute(
            select(ServerBackup)
            .filter(ServerBackup.server_id == server_id)
            .order_by(ServerBackup.created_at.desc())
        )
        return result.scalars().all()

    async def delete_backup(self, backup_id: int) -> bool:
        """删除备份记录"""
        result = await self.db_session.execute(
            select(ServerBackup)
            .filter(ServerBackup.id == backup_id)
        )
        backup = result.scalars().first()
        if not backup:
            return False

        await self.db_session.delete(backup)
        await self.db_session.commit()
        return True

    # ------------------------------
    # 服务器日志相关方法
    # ------------------------------
    async def add_server_log(self, log_data: Dict[str, Any]) -> ServerLog:
        """添加服务器日志"""
        log = ServerLog(
            server_id=log_data.get('server_id'),
            log_type=log_data.get('log_type', 'console'),
            log_level=log_data.get('log_level', 'info'),
            message=log_data.get('message'),
            source=log_data.get('source'),
            player_id=log_data.get('player_id'),
            player_name=log_data.get('player_name'),
            ip_address=log_data.get('ip_address'),
            details=log_data.get('details')
        )
        self.db_session.add(log)
        await self.db_session.commit()
        await self.db_session.refresh(log)
        return log

    async def get_server_logs(self, server_id: int, log_type: Optional[str] = None, limit: int = 100) -> List[ServerLog]:
        """获取服务器日志"""
        query = select(ServerLog).filter(ServerLog.server_id == server_id)
        if log_type:
            query = query.filter(ServerLog.log_type == log_type)

        query = query.order_by(ServerLog.created_at.desc()).limit(limit)
        result = await self.db_session.execute(query)
        return result.scalars().all()

    # ------------------------------
    # 统计相关方法
    # ------------------------------
    async def get_server_count(self, status: Optional[str] = None) -> int:
        """获取服务器数量统计"""
        query = select(Server)
        if status:
            query = query.filter(Server.status == status)

        result = await self.db_session.execute(query.count())
        return result.scalar()

    async def search_servers(self, query: str, skip: int = 0, limit: int = 50) -> List[Server]:
        """搜索服务器"""
        result = await self.db_session.execute(
            select(Server)
            .filter(
                or_(
                    Server.name.ilike(f'%{query}%'),
                    Server.description.ilike(f'%{query}%'),
                    Server.ip.ilike(f'%{query}%')
                )
            )
            .offset(skip)
            .limit(limit)
        )
        return result.scalars().all()

    async def get_server_by_ip_port(self, ip: str, port: int) -> Optional[Server]:
        """根据IP和端口获取服务器"""
        result = await self.db_session.execute(
            select(Server)
            .filter(and_(
                Server.ip == ip,
                Server.port == port
            ))
        )
        return result.scalars().first()

    async def update_server_load_order(self, server_id: int, mod_ids: List[int]) -> bool:
        """更新服务器模组加载顺序"""
        for index, mod_id in enumerate(mod_ids):
            await self.db_session.execute(
                update(ServerMod)
                .where(ServerMod.id == mod_id and ServerMod.server_id == server_id)
                .values(load_order=index)
            )

        await self.db_session.commit()
        return True

    async def toggle_mod_enabled(self, mod_id: int, enabled: bool) -> Optional[ServerMod]:
        """启用/禁用模组"""
        result = await self.db_session.execute(
            select(ServerMod)
            .filter(ServerMod.id == mod_id)
        )
        mod = result.scalars().first()
        if not mod:
            return None

        mod.enabled = enabled
        await self.db_session.commit()
        await self.db_session.refresh(mod)
        return mod

    async def get_server_with_relations(self, server_id: int) -> Optional[Server]:
        """获取服务器及其所有关联数据"""
        result = await self.db_session.execute(
            select(Server)
            .filter(Server.id == server_id)
            .options(
                selectinload(Server.configs),
                selectinload(Server.mods),
                selectinload(Server.backups),
                selectinload(Server.status_history)
            )
        )
        return result.scalars().first()

    async def get_recently_updated_servers(self, days: int = 7, limit: int = 10) -> List[Server]:
        """获取最近更新的服务器"""
        from datetime import datetime, timedelta
        cutoff_date = datetime.utcnow() - timedelta(days=days)

        result = await self.db_session.execute(
            select(Server)
            .filter(Server.updated_at >= cutoff_date)
            .order_by(Server.updated_at.desc())
            .limit(limit)
        )
        return result.scalars().all()

    async def get_server_backup_stats(self, server_id: int) -> Dict[str, Any]:
        """获取服务器备份统计信息"""
        result = await self.db_session.execute(
            select(
                func.count(ServerBackup.id),
                func.sum(ServerBackup.size),
                func.min(ServerBackup.created_at),
                func.max(ServerBackup.created_at)
            )
            .filter(ServerBackup.server_id == server_id)
        )
        count, total_size, first_backup, last_backup = result.first()

        return {
            'backup_count': count,
            'total_size': total_size,
            'first_backup': first_backup,
            'last_backup': last_backup,
            'average_size': total_size // count if count and total_size else 0
        }